import xlrd
import xlwt
import re 

target_xml = xlrd.open_workbook("电源线2020采购量统计.xls")
print("the num of worksheets is {0}".format(target_xml.nsheets))
print("worksheets name is {0}".format(target_xml.sheet_names()))
index = 0
for sheet_name in target_xml.sheet_names():
    print(sheet_name)
    sh = target_xml.sheet_by_index(index)
    index += 1
    print("sheet:{0} rows:{1} cols:{2}".format(sh.name, sh.nrows, sh.ncols))
    #print("Cell D30 is {0}".format(sh.cell_value(rowx=29, colx=3)))
    #for row in range(sh.nrows):
    #    print(sh.row(row))

    # 型号所在列5  ,5-1(数组从0开始)
    col_model = 4
    col_num = 6

    #for col in range(sh.ncols): 
    #    print(col)

    #目标excel
    model_num={}
    pattern = r"（.*?）"
    for row in range(sh.nrows):
        #print(model_num)
        model = sh.cell_value(rowx=row,colx=4)
        num = sh.cell_value(rowx=row,colx=6)
        #print(model)
        #查找
        search_result = re.search(pattern,model)
        new_model = model
        if search_result:
            print(model)
            #print(search_result.group())
            #print(model.replace(search_result.group(),''))
            new_model = model.replace(search_result.group(),'')
        #strinfo = re.compile(reg)
        #new_row = strinfo.sub("",row)
        #print(new_model)

        if new_model not in model_num:
            #print("not in")
            model_num[new_model] = num
        else:
            print("============")
            #print(new_model)
            #print("In.....")
            print(model_num[new_model])
            print(num)
            model_num[new_model] += num
             

     

    #print(model_num)

    xlspath = sheet_name+'result.xls'
    result_xml = xlwt.Workbook(encoding='utf-8')
    targetsheet = result_xml.add_sheet(sheet_name)
    result_xml.save(xlspath)

    wb = xlrd.open_workbook(xlspath)
    nrows = 0
    ncols = 0
    print(targetsheet)
    print(dir(targetsheet))
    for key in model_num:
        print(key)
        targetsheet.write(nrows,0,key)
        targetsheet.write(nrows,1,model_num[key])
        nrows += 1
    result_xml.save(xlspath)

